using System;
using System.Data;
using System.Configuration;
using Pheonix.Fashionzoomer.DAL;
using System.Data.SqlClient;
using System.Collections;
using Pheonix.CodeLibrary.Data;
using Pheonix.CodeLibrary;

/// <summary>
/// Summary description for Product
/// </summary>

namespace Pheonix.Fashionzoomer.Entity.SqlServer2000
{
    [System.ComponentModel.DataObject]
    public class Product : SqlServerDAO
    {
        #region Product Private Fields

		private Int32 _id;
		private String _productName;
		private String _productDescription;
		private String _imagePath;
		private String _defaultImageUrl;
		private Boolean _forMale;
		private Int32 _categoryId;
		private Boolean _isFeatured;
		private Int32 _totalView;
		private Int32 _shopId;
		private String _productCode;
		private DateTime _createdOn;
		private DateTime _lastUpdate;
		private Decimal _priceLow;
		private Decimal _priceHigh;
		private Int32 _stockAmount;
		private DateTime _availableFrom;
		private String _specialOffer;
		private Decimal _rating;
		private Int32 _totalRater;
		private Int32 _priority;
		private String _keywords;

        #endregion

        #region Product Constractors

		public Product(){}

		public Product
		(
			Int32 id,
			String productName,
			String productDescription,
			String imagePath,
			String defaultImageUrl,
			Boolean forMale,
			Int32 categoryId,
			Boolean isFeatured,
			Int32 totalView,
			Int32 shopId,
			String productCode,
			DateTime createdOn,
			DateTime lastUpdate,
			Decimal priceLow,
			Decimal priceHigh,
			Int32 stockAmount,
			DateTime availableFrom,
			String specialOffer,
			Decimal rating,
			Int32 totalRater,
			Int32 priority,
			String keywords

		)
		{
			this._id = id;
			this._productName = productName;
			this._productDescription = productDescription;
			this._imagePath = imagePath;
			this._defaultImageUrl = defaultImageUrl;
			this._forMale = forMale;
			this._categoryId = categoryId;
			this._isFeatured = isFeatured;
			this._totalView = totalView;
			this._shopId = shopId;
			this._productCode = productCode;
			this._createdOn = createdOn;
			this._lastUpdate = lastUpdate;
			this._priceLow = priceLow;
			this._priceHigh = priceHigh;
			this._stockAmount = stockAmount;
			this._availableFrom = availableFrom;
			this._specialOffer = specialOffer;
			this._rating = rating;
			this._totalRater = totalRater;
			this._priority = priority;
			this._keywords = keywords;
		}

        #endregion

        #region Product Properties

		public Int32 Id
		{
			get{ return this._id; }
			set{ this._id = value; }
		}

		public String ProductName
		{
			get{ return this._productName; }
			set{ this._productName = value; }
		}

		public String ProductDescription
		{
			get{ return this._productDescription; }
			set{ this._productDescription = value; }
		}

		public String ImagePath
		{
			get{ return this._imagePath; }
			set{ this._imagePath = value; }
		}

		public String DefaultImageUrl
		{
			get{ return this._defaultImageUrl; }
			set{ this._defaultImageUrl = value; }
		}

		public Boolean ForMale
		{
			get{ return this._forMale; }
			set{ this._forMale = value; }
		}

		public Int32 CategoryId
		{
			get{ return this._categoryId; }
			set{ this._categoryId = value; }
		}

		public Boolean IsFeatured
		{
			get{ return this._isFeatured; }
			set{ this._isFeatured = value; }
		}

		public Int32 TotalView
		{
			get{ return this._totalView; }
			set{ this._totalView = value; }
		}

		public Int32 ShopId
		{
			get{ return this._shopId; }
			set{ this._shopId = value; }
		}

		public String ProductCode
		{
			get{ return this._productCode; }
			set{ this._productCode = value; }
		}

		public DateTime CreatedOn
		{
			get{ return this._createdOn; }
			set{ this._createdOn = value; }
		}

		public DateTime LastUpdate
		{
			get{ return this._lastUpdate; }
			set{ this._lastUpdate = value; }
		}

		public Decimal PriceLow
		{
			get{ return this._priceLow; }
			set{ this._priceLow = value; }
		}

		public Decimal PriceHigh
		{
			get{ return this._priceHigh; }
			set{ this._priceHigh = value; }
		}

		public Int32 StockAmount
		{
			get{ return this._stockAmount; }
			set{ this._stockAmount = value; }
		}

		public DateTime AvailableFrom
		{
			get{ return this._availableFrom; }
			set{ this._availableFrom = value; }
		}

		public String SpecialOffer
		{
			get{ return this._specialOffer; }
			set{ this._specialOffer = value; }
		}

		public Decimal Rating
		{
			get{ return this._rating; }
			set{ this._rating = value; }
		}

		public Int32 TotalRater
		{
			get{ return this._totalRater; }
			set{ this._totalRater = value; }
		}

		public Int32 Priority
		{
			get{ return this._priority; }
			set{ this._priority = value; }
		}

		public String Keywords
		{
			get{ return this._keywords; }
			set{ this._keywords = value; }
		}


        #endregion

        #region Product Static Methods

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public static Int32 CreateNewProduct
        (
			String productName,
			String productDescription,
			String imagePath,
			String defaultImageUrl,
			Boolean forMale,
			Int32 categoryId,
			Boolean isFeatured,
			Int32 totalView,
			Int32 shopId,
			String productCode,
			DateTime createdOn,
			DateTime lastUpdate,
			Decimal priceLow,
			Decimal priceHigh,
			Int32 stockAmount,
			DateTime availableFrom,
			String specialOffer,
			Decimal rating,
			Int32 totalRater,
			Int32 priority,
			String keywords

        )
        {
            //creating an empty Product object
            Product product = new Product();
            product.ProductName = productName;
			product.ProductDescription = productDescription;
			product.ImagePath = imagePath;
			product.DefaultImageUrl = defaultImageUrl;
			product.ForMale = forMale;
			product.CategoryId = categoryId;
			product.IsFeatured = isFeatured;
			product.TotalView = totalView;
			product.ShopId = shopId;
			product.ProductCode = productCode;
			product.CreatedOn = createdOn;
			product.LastUpdate = lastUpdate;
			product.PriceLow = priceLow;
			product.PriceHigh = priceHigh;
			product.StockAmount = stockAmount;
			product.AvailableFrom = availableFrom;
			product.SpecialOffer = specialOffer;
			product.Rating = rating;
			product.TotalRater = totalRater;
			product.Priority = priority;
			product.Keywords = keywords;

            return Product.CreateNewProduct(product);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool UpdateProduct
        (
			Int32 id,
			String productName,
			String productDescription,
			String imagePath,
			String defaultImageUrl,
			Boolean forMale,
			Int32 categoryId,
			Boolean isFeatured,
			Int32 totalView,
			Int32 shopId,
			String productCode,
			DateTime createdOn,
			DateTime lastUpdate,
			Decimal priceLow,
			Decimal priceHigh,
			Int32 stockAmount,
			DateTime availableFrom,
			String specialOffer,
			Decimal rating,
			Int32 totalRater,
			Int32 priority,
			String keywords

        )
        {
            Product product = Product.GetProductById(id);
            product.ProductName = productName;
			product.ProductDescription = productDescription;
			product.ImagePath = imagePath;
			product.DefaultImageUrl = defaultImageUrl;
			product.ForMale = forMale;
			product.CategoryId = categoryId;
			product.IsFeatured = isFeatured;
			product.TotalView = totalView;
			product.ShopId = shopId;
			product.ProductCode = productCode;
			product.CreatedOn = createdOn;
			product.LastUpdate = lastUpdate;
			product.PriceLow = priceLow;
			product.PriceHigh = priceHigh;
			product.StockAmount = stockAmount;
			product.AvailableFrom = availableFrom;
			product.SpecialOffer = specialOffer;
			product.Rating = rating;
			product.TotalRater = totalRater;
			product.Priority = priority;
			product.Keywords = keywords;

            return Product.UpdateProduct(product);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool DeleteProduct
        (
            Int32 id
        )
        {
            //Validate Input
            if (id <= GetIdMinValue)
                throw (new ArgumentOutOfRangeException("id in Product"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, id);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_DELETEPRODUCT);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteProductList(System.Collections.Generic.List<Int32> idListToDelete)
        {
            //Validate Input
            foreach (Int32 id in idListToDelete)
                if (id <= GetIdMinValue)
                    throw (new ArgumentOutOfRangeException("id in Product"));

            //performing deletion operation //

            string csvString = Utility.MakeCSV(idListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@IdList", SqlDbType.NVarChar, csvString.Length, 
                ParameterDirection.Input, csvString);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_DELETEPRODUCTLIST);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == idListToDelete.Count ? true : false);
        }
		
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
		public static CustomCollection<Product> GetProductByCategoryId(Int32 categoryId)
		{
			if(categoryId <= GetIdMinValue)
				throw (new ArgumentOutOfRangeException("categoryId in Product"));

			SqlCommand sqlCmd = new SqlCommand();
			AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
			SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETPRODUCTBYCATEGORYID);
			GenerateCollectionFromReader dg = new GenerateCollectionFromReader(GenerateProductCollectionFromReader);
			CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, dg));

			if (objCollection.Count > 0)
				return objCollection;
			else
				return null;
		}

		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
		public static CustomCollection<Product> GetProductByShopId(Int32 shopId)
		{
			if(shopId <= GetIdMinValue)
				throw (new ArgumentOutOfRangeException("shopId in Product"));

			SqlCommand sqlCmd = new SqlCommand();
			AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);
			SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETPRODUCTBYSHOPID);
			GenerateCollectionFromReader dg = new GenerateCollectionFromReader(GenerateProductCollectionFromReader);
			CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, dg));

			if (objCollection.Count > 0)
				return objCollection;
			else
				return null;
		}

        #endregion

        #region Product Instance Methods
        
        public bool Save()
		{
			if(Id <= GetIdMinValue)
			{
				Int32 TempId=CreateNewProduct(this);
				if(TempId>0)
				{
					_id=TempId;
					return true;
				}
				else
				{
					// error occurs
					return false;
				}
			}
			else
			{	//This is a update Command
				return (UpdateProduct(this));
			}
		}

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<Product> GetAllProduct()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETALLPRODUCT);
            GenerateCollectionFromReader result = new GenerateCollectionFromReader(GenerateProductCollectionFromReader);
            CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, result));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<Product> GetAllProductPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "Id";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startRowIndex", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@maximumRows", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETALLPRODUCTPAGED);

            GenerateCollectionFromReader result = new GenerateCollectionFromReader(GenerateProductCollectionFromReader);
            CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, result));
            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static int GetAllProductPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETALLPRODUCTPAGEDCOUNT);
            GetOneData(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static Product GetProductById(Int32 id)
        {
            if (id <= GetIdMinValue)
                throw (new ArgumentOutOfRangeException("id in Product"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, id);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_GETPRODUCTBYID);
            GenerateCollectionFromReader result = new GenerateCollectionFromReader(GenerateProductCollectionFromReader);
            CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, result));

            if (objCollection.Count > 0)
                return (Product)objCollection[0];
            else
                return null;
        }

        public static Int32 CreateNewProduct(Product newProduct)
        {
            // validate Parameters
            if (newProduct == null)
                throw (new ArgumentNullException("newProduct in Product"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 64, ParameterDirection.Input, newProduct.ProductName);
			AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.NText, 0, ParameterDirection.Input, newProduct.ProductDescription);
			AddParameterToSqlCmd(sqlCmd, "@ImagePath", SqlDbType.NVarChar, 250, ParameterDirection.Input, newProduct.ImagePath);
			AddParameterToSqlCmd(sqlCmd, "@DefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newProduct.DefaultImageUrl);
			AddParameterToSqlCmd(sqlCmd, "@ForMale", SqlDbType.Bit , 0, ParameterDirection.Input, newProduct.ForMale);
			AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.CategoryId);
			AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit , 0, ParameterDirection.Input, newProduct.IsFeatured);
			AddParameterToSqlCmd(sqlCmd, "@TotalView", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.TotalView);
			AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.ShopId);
			AddParameterToSqlCmd(sqlCmd, "@ProductCode", SqlDbType.NVarChar, 250, ParameterDirection.Input, newProduct.ProductCode);
			AddParameterToSqlCmd(sqlCmd, "@CreatedOn", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.CreatedOn);
			AddParameterToSqlCmd(sqlCmd, "@LastUpdate", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.LastUpdate);
			AddParameterToSqlCmd(sqlCmd, "@PriceLow", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.PriceLow);
			AddParameterToSqlCmd(sqlCmd, "@PriceHigh", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.PriceHigh);
			AddParameterToSqlCmd(sqlCmd, "@StockAmount", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.StockAmount);
			AddParameterToSqlCmd(sqlCmd, "@AvailableFrom", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.AvailableFrom);
			AddParameterToSqlCmd(sqlCmd, "@SpecialOffer", SqlDbType.NText, 0, ParameterDirection.Input, newProduct.SpecialOffer);
			AddParameterToSqlCmd(sqlCmd, "@Rating", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.Rating);
			AddParameterToSqlCmd(sqlCmd, "@TotalRater", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.TotalRater);
			AddParameterToSqlCmd(sqlCmd, "@Priority", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.Priority);
			AddParameterToSqlCmd(sqlCmd, "@Keywords", SqlDbType.NVarChar, 512, ParameterDirection.Input, newProduct.Keywords);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_CREATENEWPRODUCT);
            GetOneData(sqlCmd);
            return ((Int32)sqlCmd.Parameters["@ReturnValue"].Value);

        }

        public static bool UpdateProduct(Product newProduct)
        {
            // validate Parameters
            if (newProduct == null)
                throw (new ArgumentNullException("newProduct"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.Id);
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 64, ParameterDirection.Input, newProduct.ProductName);
			AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.NText, 0, ParameterDirection.Input, newProduct.ProductDescription);
			AddParameterToSqlCmd(sqlCmd, "@ImagePath", SqlDbType.NVarChar, 250, ParameterDirection.Input, newProduct.ImagePath);
			AddParameterToSqlCmd(sqlCmd, "@DefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newProduct.DefaultImageUrl);
			AddParameterToSqlCmd(sqlCmd, "@ForMale", SqlDbType.Bit , 0, ParameterDirection.Input, newProduct.ForMale);
			AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.CategoryId);
			AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit , 0, ParameterDirection.Input, newProduct.IsFeatured);
			AddParameterToSqlCmd(sqlCmd, "@TotalView", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.TotalView);
			AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.ShopId);
			AddParameterToSqlCmd(sqlCmd, "@ProductCode", SqlDbType.NVarChar, 256, ParameterDirection.Input, newProduct.ProductCode);
			AddParameterToSqlCmd(sqlCmd, "@CreatedOn", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.CreatedOn);
			AddParameterToSqlCmd(sqlCmd, "@LastUpdate", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.LastUpdate);
			AddParameterToSqlCmd(sqlCmd, "@PriceLow", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.PriceLow);
			AddParameterToSqlCmd(sqlCmd, "@PriceHigh", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.PriceHigh);
			AddParameterToSqlCmd(sqlCmd, "@StockAmount", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.StockAmount);
			AddParameterToSqlCmd(sqlCmd, "@AvailableFrom", SqlDbType.DateTime, 0, ParameterDirection.Input, newProduct.AvailableFrom);
			AddParameterToSqlCmd(sqlCmd, "@SpecialOffer", SqlDbType.NText, 0, ParameterDirection.Input, newProduct.SpecialOffer);
			AddParameterToSqlCmd(sqlCmd, "@Rating", SqlDbType.Decimal, 0, ParameterDirection.Input, newProduct.Rating);
			AddParameterToSqlCmd(sqlCmd, "@TotalRater", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.TotalRater);
			AddParameterToSqlCmd(sqlCmd, "@Priority", SqlDbType.Int, 0, ParameterDirection.Input, newProduct.Priority);
			AddParameterToSqlCmd(sqlCmd, "@Keywords", SqlDbType.NVarChar, 512, ParameterDirection.Input, newProduct.Keywords);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_PRODUCT_UPDATEPRODUCT);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        #endregion

        #region Product Related Collections
        /// <summary>
        /// Creates and returns a strongly typed collection of Product custom entity. 
        /// The colelction is created through iterating on the IdataReader object which contains 
        /// Product information, as a set of records, similar to tabular format.
        /// </summary>
        public static IList GenerateProductCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Product collection
            CustomCollection<Product> colProduct = new CustomCollection<Product>();

            //Iterating thriugh the data reader, to generate Product collection.

            //getting the Product collection by iterating thru the data reader
            while (returnData.Read())
            {
                //passing the Product constructor parameters from the current instance of data reader fields.
                Product newProduct = new Product
                (
                    returnData["id"] == System.DBNull.Value ? GetIdMinValue : (Int32)returnData["id"],
                    returnData["product_name"] == System.DBNull.Value ? String.Empty : (String)returnData["product_name"],
					returnData["product_description"] == System.DBNull.Value ? String.Empty : (String)returnData["product_description"],
					returnData["image_path"] == System.DBNull.Value ? String.Empty : (String)returnData["image_path"],
					returnData["default_image_url"] == System.DBNull.Value ? String.Empty : (String)returnData["default_image_url"],
					returnData["for_male"] == System.DBNull.Value ? false : (Boolean)returnData["for_male"],
					returnData["category_id"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["category_id"],
					returnData["is_featured"] == System.DBNull.Value ? false : (Boolean)returnData["is_featured"],
					returnData["total_view"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["total_view"],
					returnData["shop_id"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["shop_id"],
					returnData["product_code"] == System.DBNull.Value ? String.Empty : (String)returnData["product_code"],
					returnData["created_on"] == System.DBNull.Value ? DateTime.MinValue : (DateTime)returnData["created_on"],
					returnData["last_update"] == System.DBNull.Value ? DateTime.MinValue : (DateTime)returnData["last_update"],
					returnData["price_low"] == System.DBNull.Value ? Decimal.MinValue : (Decimal)returnData["price_low"],
					returnData["price_high"] == System.DBNull.Value ? Decimal.MinValue : (Decimal)returnData["price_high"],
					returnData["stock_amount"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["stock_amount"],
					returnData["available_from"] == System.DBNull.Value ? DateTime.MinValue : (DateTime)returnData["available_from"],
					returnData["special_offer"] == System.DBNull.Value ? String.Empty : (String)returnData["special_offer"],
					returnData["rating"] == System.DBNull.Value ? Decimal.MinValue : (Decimal)returnData["rating"],
					returnData["total_rater"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["total_rater"],
					returnData["priority"] == System.DBNull.Value ? Int32.MinValue : (Int32)returnData["priority"],
					returnData["keywords"] == System.DBNull.Value ? String.Empty : (String)returnData["keywords"]

                );
                //adding the Product to the collection
                colProduct.Add(newProduct);
            }

            //returns the collection of Product 
            return (colProduct);
        }

        #endregion

        #region Constants and Default Values

        public static Int32 GetIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //contants
        internal const string SPR_PRODUCT_CREATENEWPRODUCT = "spr_Product_CreateNewProduct";
        internal const string SPR_PRODUCT_UPDATEPRODUCT = "spr_Product_UpdateProduct";
        internal const string SPR_PRODUCT_DELETEPRODUCT = "spr_Product_DeleteProduct";
        internal const string SPR_PRODUCT_DELETEPRODUCTLIST = "spr_Product_DeleteProductList";
        internal const string SPR_PRODUCT_GETPRODUCTBYID = "spr_Product_GetProductById";
        internal const string SPR_PRODUCT_GETALLPRODUCT = "spr_Product_GetAllProduct";
        internal const string SPR_PRODUCT_GETALLPRODUCTPAGED = "spr_Product_GetAllProductPaged";
        internal const string SPR_PRODUCT_GETALLPRODUCTPAGEDCOUNT = "spr_Product_GetAllProductPagedCount";

		//Foreign key contants
		internal const string SPR_PRODUCT_GETPRODUCTBYCATEGORYID="spr_Product_GetProductByCategoryId";
		internal const string SPR_PRODUCT_GETPRODUCTBYCATEGORYIDPAGED="spr_Product_GetProductByCategoryIdPaged";
		internal const string SPR_PRODUCT_GETPRODUCTBYCATEGORYIDPAGEDCOUNT="spr_Product_GetProductByCategoryIdPagedCount";
		internal const string SPR_PRODUCT_GETPRODUCTBYSHOPID="spr_Product_GetProductByShopId";
		internal const string SPR_PRODUCT_GETPRODUCTBYSHOPIDPAGED="spr_Product_GetProductByShopIdPaged";
		internal const string SPR_PRODUCT_GETPRODUCTBYSHOPIDPAGEDCOUNT="spr_Product_GetProductByShopIdPagedCount";
		
        #endregion
    }
}
